package win.doyto.service.data.report.support;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import win.doyto.service.data.config.ReportConfigEntity;
import win.doyto.service.data.config.ReportParam;
import win.doyto.service.data.report.ReportMapper;
import win.doyto.service.data.report.ReportService;

import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;

import static org.apache.commons.lang3.ArrayUtils.EMPTY_STRING_ARRAY;
import static win.doyto.service.data.report.ReportMapper.SHOW_ROW_NUMBER;
import static win.doyto.service.data.report.ReportMapper.SQL_KEY;

/**
 * ReportXlsService
 *
 * @author f0rb on 2017-12-20.
 */
@Slf4j
@Service("reportXlsService")
public class ReportXlsService implements ReportService {

    public static final Integer SHEET_MAX_ROW = 10000;
    public static final Integer TITLE_ROW_IDX = 0;

    @Resource
    private ReportMapper reportMapper;

    @Override
    @Transactional
    public String fetchAndWrite(OutputStream output, List<ReportConfigEntity> reportConfigEntities, Map<String, Object> params) {

        Workbook workbook = createWorkbook();

        int index = 0;
        for (ReportConfigEntity reportConfigEntity : reportConfigEntities) {
            ReportParam reportParam = reportConfigEntity.reportParam();
            if (!reportParam.isContentTypeSupported(getExtension())) continue;

            //创建sheet
            String sheetname = StringUtils.defaultIfBlank(reportParam.getXlsSheetName(), "sheet_" + (++index));
            params.put(SQL_KEY, reportConfigEntity.getReportSql());
            writeSheet(workbook, sheetname, params, reportConfigEntity.getReportSql());
        }
        String filename = reportConfigEntities.get(0).getReportName();
        //文件名格式化
        filename = filename.replace("%D", DateFormatUtils.format(new Date(), "yyyyMMdd"));


        try {
            workbook.write(output);
        } catch (IOException e) {
            log.error("OutputStream", e);
        }
        return filename;
    }

    private void writeSheet(Workbook workbook, String sheetname, Map<String, Object> params, String reportSql) {

        Iterable<LinkedHashMap<String, Object>> data = params.containsKey(SHOW_ROW_NUMBER) ?
                reportMapper.getDataWithRowNum(params) : reportMapper.getData(params);

        int sheetIdx = 1;
        Sheet sheet = workbook.createSheet(sheetname);
        sheet.setDefaultColumnWidth(10);
        sheet.setDefaultRowHeight((short) 300);

        String[] titles = null;
        int row = 0;
        for (LinkedHashMap<String, Object> result : data) {
            if (row % 500 == 0 && log.isDebugEnabled()) {
                log.debug("已处理{}行数据", row + (sheetIdx - 1) * SHEET_MAX_ROW);
            }
            if (row == 0) {
                titles = result.keySet().toArray(EMPTY_STRING_ARRAY);
            }
            row++;//数据从第一行开始填充
            writeRow(sheet.createRow(row), result.values().toArray());

            if (row >= SHEET_MAX_ROW) {//每个sheet最多SHEET_MAX_ROW行数据, xls最多可以有65535行
                //填充前一个sheet的标题
                writeTitle(sheet, titles);
                sheet = workbook.createSheet(sheetname + "_" + sheetIdx);
                sheetIdx++;
                row = 0;
            }
        }
        log.debug("总计处理{}行数据!", row + (sheetIdx - 1) * SHEET_MAX_ROW);
        titles = checkTitle(params, titles, reportSql);
        writeTitle(sheet, titles);
        log.info("{}共有{}列", sheet.getSheetName(), sheet.getRow(TITLE_ROW_IDX).getLastCellNum());
    }

    private String[] checkTitle(Map<String, Object> params, String[] titles, String reportSql) {
        //没有数据时再从数据库查一行空数据
        if (titles == null) {
            params.put(SQL_KEY, reportSql.substring(0, reportSql.lastIndexOf("LIMIT")));
            LinkedHashMap<String, Object> empty = reportMapper.getNullRowForTitle(params);
            if (empty != null) {
                titles = empty.keySet().toArray(EMPTY_STRING_ARRAY);
            } else {
                titles = new String[]{"未查询到有效数据"};
            }
        }
        return titles;
    }

    private void writeTitle(Sheet sheet, String[] titles) {
        //填充最后一个sheet的标题
        sheet.createRow(TITLE_ROW_IDX);
        for (int col = 0; col < titles.length; col++) {
            sheet.getRow(TITLE_ROW_IDX).createCell(col).setCellValue(titles[col]);
            sheet.autoSizeColumn(col);
        }
    }

    private void writeRow(Row row, Object[] dataRow) {
        for (int col = 0; col < dataRow.length; col++) {
            if (dataRow[col] != null) {
                if (dataRow[col] instanceof Number) {
                    if (dataRow[col] instanceof Double) {
                        row.createCell(col).setCellValue((Double) dataRow[col]);
                    } else {
                        row.createCell(col).setCellValue(Double.parseDouble(String.valueOf(dataRow[col])));
                    }
                } else {
                    row.createCell(col).setCellValue(String.valueOf(dataRow[col]));
                }

            }
        }
    }

    @Override
    public String getExtension() {
        return "xls";
    }

    protected Workbook createWorkbook() {
        return new HSSFWorkbook();
    }

}
